# -*- coding:utf-8 -*-
# @Date    : 2017-08-14 15:19:56
# @Function: 把用户简历Excel表单转化为json文件
# @Author  : wenyq

import xlrd
import json
import codecs
import os


# 把excel表格中指定sheet转为json
def Excel2Json(file_path):
    # 打开excel文件
    if get_data(file_path) is not None:
        book = get_data(file_path)
        # 抓取所有sheet页的名称
        worksheets = book.sheet_names()
        print("该Excel包含的表单列表为：\n")
        for sheet in worksheets:
            print('%s,%s' % (worksheets.index(sheet), sheet))
        # inp = input(u'请输入表单名对应的编号，对应表单将自动转为json:\n')
        inp = 0  # 0: 第一个sheets，1: 第二个sheets，以此类推………
        sheet = book.sheet_by_index(inp)
        row_0 = sheet.row(0)  # 第一行是表单标题
        nrows = sheet.nrows  # 行号
        ncols = sheet.ncols  # 列号

        result = {}  # 定义json对象
        result["title"] = file_path  # 表单标题
        result["rows"] = nrows  # 行号
        result["children"] = []  # 每一行作为数组的一项
        # 遍历所有行，将excel转化为json对象
        for i in range(nrows):
            if i == 0:
                continue
            tmp = {}
            # 遍历当前行所有列
            for j in range(ncols):
                # 获取当前列中文标题
                title_de = str(row_0[j])
                title_cn = title_de.split("'")[1]
                # 获取单元格的值
                tmp[title_cn] = sheet.row_values(i)[j]
            result["children"].append(tmp)
        # print(json.loads(result['children'][0]['extracurricular_practice']))

        num1 = loads_json(result=result, key='education_background')  # education_background
        num2 = loads_json(result=result, key='extracurricular_practice')  # extracurricular_practice
        num3 = loads_json(result=result, key='internship_intention')  # internship_intention
        num4 = loads_json(result=result, key='project_experience')  # project_experience
        num5 = loads_json(result=result, key='work_experience')  # work_experience
        num = num1+num2+num3+num4+num5
        print("总计：", num)


        json_data = json.dumps(result, indent=4, sort_keys=True)
        saveFile(os.getcwd(), worksheets[inp], json_data)
    pass


def loads_json(result, key):
    num = 0
    uid = []
    for i in range(len(result["children"])):
        if result["children"][i][key] != '':
            try:
                result["children"][i][key] = json.loads(
                    result["children"][i][key])
            except Exception as e:
                num += 1
                uid.append(result["children"][i]['uid'])
    print("数据格式错误总计：", num, "------",key)
    print("错误数据对应uid：", uid)
    print("\n")
    return num


def get_data(file_path):
    """获取excel数据源"""
    try:
        data = xlrd.open_workbook(file_path)
        return data
    except Exception as e:
        print(u'excel表格读取失败：%s' % e)
        return None


def saveFile(file_path, file_name, data):
    output = codecs.open(file_path + "/" + file_name + ".json", 'w', "utf-8")
    output.write(data)
    output.close()


if __name__ == '__main__':
    # file_path = "orion_user_resume.xlsx"
    file_path = "test.xlsx"
    json_data = Excel2Json(file_path)